package uit.bus;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBAdapter {

	public static final String DB_NAME = "bus";
	public static final int DB_VERSION = 1;

	public static final String CREATE_TB_TUYEN = "create table tuyen (matuyen integer primary key,"
			+ "tentuyen text)";

	public static final String CREATE_TB_LUOT = "create table luot (maluot integer primary key "
			+ ",tenluot text)";

	public static final String CREATE_TB_TRAM = "create table tram (matram text primary key,"
			+ "kinhdo double,vido double)";

	public static final String CREATE_TB_TRAM1 = "create table tram1 (matram text primary key,"
			+ "vido double,kinhdo double,tuyenqua text)";

	public static final String CREATE_TB_HANHTRINH = "create table hanhtrinh (matuyen integer,"
			+ "sothutu integer,matram text,maluot integer)";

	public static final String CREATE_TB_THONGTIN = "create table thongtin (matuyen integer primary key,"
			+ "luotdi text, luotve text, thongtinkhac text, donvidamnhan text)";

	public final Context context;
	public DatabaseHelper DBHelper;
	public SQLiteDatabase db;

	public DBAdapter(Context context) {
		super();
		this.context = context;
		DBHelper = new DatabaseHelper(context);
	}

	public static class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			try {
				db.execSQL(CREATE_TB_HANHTRINH);
				db.execSQL(CREATE_TB_LUOT);
				db.execSQL(CREATE_TB_THONGTIN);
				db.execSQL(CREATE_TB_TRAM);
				db.execSQL(CREATE_TB_TRAM1);
				db.execSQL(CREATE_TB_TUYEN);
			} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			db.execSQL("DROP TABLE IF EXISTS hanhtrinh");
			db.execSQL("DROP TABLE IF EXISTS luot");
			db.execSQL("DROP TABLE IF EXISTS thongtin");
			db.execSQL("DROP TABLE IF EXISTS tram");
			db.execSQL("DROP TABLE IF EXISTS tram1");
			db.execSQL("DROP TABLE IF EXISTS tuyen");
			onCreate(db);
		}
	}

	public DBAdapter open() {
		db = DBHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		db.close();
	}

	public Cursor getListTuyen() {
		String SQL = "select * from tuyen";
		Cursor mycursor = db.rawQuery(SQL, null);
		return mycursor;
	}

	public Cursor getTuyenCT(int matuyen) {
		String sql = "select * from tuyen,thongtin where tuyen.matuyen = thongtin.matuyen and tuyen.matuyen = "
				+ matuyen;
		Cursor mycursor = db.rawQuery(sql, null);
		return mycursor;
	}

	public Cursor getTuyen(int matuyen) {
		String sql = "select * from tuyen where matuyen = " + matuyen;
		Cursor mycursor = db.rawQuery(sql, null);
		return mycursor;
	}

	public Cursor getLoTrinh(int matuyen, int maluot) {
		String sql = "select * from hanhtrinh,tram where matuyen = " + matuyen
				+ " and maluot = " + maluot
				+ " and hanhtrinh.matram = tram.matram";
		Cursor mycursor = db.rawQuery(sql, null);
		return mycursor;
	}

	public Cursor getListTram() {
		String sql = "select * from tram";
		Cursor mycursor = db.rawQuery(sql, null);
		return mycursor;
	}

	//
	// public void SaveKQ(int maluot,String tenluot) {
	// ContentValues values = new ContentValues();
	// values.put("maluot", maluot);
	// values.put("tenluot", tenluot);
	// db.insert("luot", null, values);
	// }

	public boolean CheckDiaDiem(String diadiem) {
		String sql = "select * from tram where matram = '" + diadiem + "'";
		Cursor myCursor = db.rawQuery(sql, null);
		if (myCursor.moveToFirst()) {
			return true;
		} else {
			return false;
		}
	}

	public Cursor getLatLongDD(String diadiem) {
		String sql = "select * from tram where matram = '" + diadiem + "'";
		Cursor myCursor = db.rawQuery(sql, null);
		return myCursor;
	}

	public Cursor timtuyen(String diadiem) {
		String sql = "select distinct matuyen from hanhtrinh where matram = '"
				+ diadiem + "'";
		Cursor mycursor = db.rawQuery(sql, null);
		return mycursor;
	}

	public Cursor getdiemchuyen(int matuyen1, int matuyen2) {
		String sql = "select * from hanhtrinh where matuyen = "
				+ matuyen1
				+ " and matram in (select matram from hanhtrinh where matuyen = "
				+ matuyen2 + ") group by matram";
		Cursor mycursor = db.rawQuery(sql, null);
		return mycursor;
	}

	public Cursor getMaluot(String matram, int matuyen) {
		String sql = "select * from hanhtrinh where matram ='" + matram
				+ "' and matuyen =" + matuyen;
		Cursor mycursor = db.rawQuery(sql, null);
		return mycursor;
	}

	public Cursor getListShowOnMap(int matuyen, int sttdi, int sttden,
			int maluot) {
		String sql = "select * from hanhtrinh,tram where hanhtrinh.matram = tram.matram and matuyen = "
				+ matuyen
				+ " and sothutu between+"
				+ sttdi
				+ " and "
				+ sttden
				+ " and maluot =" + maluot;
		Cursor mycursor = db.rawQuery(sql, null);
		return mycursor;
	}

}